import os
from flask import Flask, render_template, request, redirect, url_for, flash
from flask_sqlalchemy import SQLAlchemy
from flask_login import LoginManager, UserMixin, login_user, login_required, logout_user, current_user
from werkzeug.security import generate_password_hash, check_password_hash
from datetime import datetime, timedelta
from sqlalchemy import inspect
from ldap3 import Server, Connection, AUTO_BIND_NO_TLS, NTLM

app = Flask(__name__, template_folder='templates')
app.config['SECRET_KEY'] = 'your_secret_key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///tickets.db'
db = SQLAlchemy(app)
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'

# 数据库模型
class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(100), unique=True, nullable=False)
    password_hash = db.Column(db.String(100), nullable=False)

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

class Ticket(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    category = db.Column(db.String(50), nullable=False)
    assignee = db.Column(db.String(100), nullable=False)
    status = db.Column(db.String(20), nullable=False, default='Pending')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    creator = db.Column(db.String(100), nullable=False)

class task(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    project = db.Column(db.String(100), nullable=False)
    operator = db.Column(db.String(100), nullable=False)

# 初始化数据库
@app.before_request
def create_tables():
    inspector = inspect(db.engine)
    if not inspector.get_table_names():
        db.create_all()

@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))

# AD 验证函数
def validate_ad_credentials(username, password):
    # 从环境变量中获取 LDAP 配置
    ldap_server = os.getenv('LDAP_SERVER', 'oa.******.com')
    ldap_port = int(os.getenv('LDAP_PORT', 389))  # 使用非 SSL 端口
    ldap_domain = os.getenv('LDAP_DOMAIN', '*****.com')

    # 构造完整的用户 DN，格式为 domain\username
    user_dn = f'{ldap_domain}\\{username}'

    server = Server(ldap_server, port=ldap_port)
    connection = Connection(server, user=user_dn, password=password, authentication=NTLM, auto_bind=AUTO_BIND_NO_TLS)

    if connection.bind():
        return True
    return False

def flash_if_not_logging_out(message):
    if request.path != '/logout':
        flash(message)

## 数据插入功能
@app.route('/add_tasks', methods=['GET', 'POST'])
@login_required
def add_tasks():
    if request.method == 'POST':
        # 获取表单数据
        project = request.form['project']
        operator = request.form['operator']

        # 创建一个新的 task 对象
        new_task = task(project=project, operator=operator)

        # 将新对象添加到会话中
        db.session.add(new_task)

        # 提交会话，保存到数据库
        db.session.commit()

        flash('Task added successfully')
        return redirect(url_for('add_tasks'))

    return render_template('add_tasks.html')


# 插入示例数据
@app.route('/insert_example_data')
@login_required
def insert_example_data():
    # 插入示例数据
    tasks = [
        {'project': '科研之友', 'operator': 'ck'},
        {'project': '广州卫生', 'operator': 'ck'},
        # 添加更多示例数据
    ]

    for task_data in tasks:
        new_task = task(project=task_data['project'], operator=task_data['operator'])
        db.session.add(new_task)

    db.session.commit()

    flash('Example data inserted successfully')
    return redirect(url_for('dashboard'))

# 登录页面
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']

        # 验证 AD 域账号
        if validate_ad_credentials(username, password):
            # 如果 AD 验证成功，检查本地数据库中是否有该用户
            user = User.query.filter_by(username=username).first()
            if not user:
                # 如果本地数据库中没有该用户，创建一个新用户
                user = User(username=username)
                user.set_password(password)
                db.session.add(user)
                db.session.commit()
            login_user(user)
            return redirect(url_for('dashboard'))
        else:
            flash('Invalid username or password')
    return render_template('login.html')

# 登出
@app.route('/logout')
@login_required
def logout():
    logout_user()
    return redirect(url_for('login'))

# 主页
# @app.route('/')
# @login_required
# def dashboard():
#     page = request.args.get('page', 1, type=int)  # 获取当前页码，默认为1
#     per_page = request.args.get('per_page', 3, type=int)  # 获取每页显示条数，默认为3
#     pagination = Ticket.query.paginate(page=page, per_page=per_page, error_out=False)
#     tickets = pagination.items
#     return render_template('dashboard.html', tickets=tickets, pagination=pagination)

@app.route('/')
@login_required
def dashboard():
    page = request.args.get('page', 1, type=int)  # 获取当前页码，默认为1
    per_page = request.args.get('per_page', 10, type=int)  # 获取每页显示条数，默认为10
    pagination = Ticket.query.paginate(page=page, per_page=per_page, error_out=False)
    tickets = pagination.items
    return render_template('dashboard.html', tickets=tickets, pagination=pagination)


# 新建工单
@app.route('/ticket/new', methods=['GET', 'POST'])
@login_required
def new_ticket():
    if request.method == 'POST':
        name = request.form['name']
        content = request.form['content']
        category = request.form['category']
        assignee = request.form['assignee']
        ticket = Ticket(name=name, content=content, category=category, assignee=assignee, creator=current_user.username)
        db.session.add(ticket)
        db.session.commit()
        flash('Ticket created successfully')
        return redirect(url_for('dashboard'))
    return render_template('new_ticket.html')

# 编辑工单
@app.route('/ticket/edit/<int:ticket_id>', methods=['GET', 'POST'])
@login_required
def edit_ticket(ticket_id):
    ticket = Ticket.query.get_or_404(ticket_id)
    if request.method == 'POST':
        ticket.name = request.form['name']
        ticket.content = request.form['content']
        ticket.category = request.form['category']
        ticket.assignee = request.form['assignee']
        ticket.status = request.form['status']
        db.session.commit()
        flash('Ticket updated successfully')
        return redirect(url_for('dashboard'))
    return render_template('edit_ticket.html', ticket=ticket)

# 工单明细
@app.route('/ticket/view/<int:ticket_id>')
@login_required
def view_ticket(ticket_id):
    ticket = Ticket.query.get_or_404(ticket_id)
    return render_template('view_ticket.html', ticket=ticket)

# 日结统计
@app.route('/report')
@login_required
def report():
    today = datetime.utcnow().date()
    tickets = Ticket.query.filter(Ticket.created_at >= today).all()
    assignee_report = {}
    category_report = {}
    for ticket in tickets:
        if ticket.assignee in assignee_report:
            assignee_report[ticket.assignee] += 1
        else:
            assignee_report[ticket.assignee] = 1
        if ticket.category in category_report:
            category_report[ticket.category] += 1
        else:
            category_report[ticket.category] = 1
    return render_template('report.html', assignee_report=assignee_report, category_report=category_report)

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5000)